
clear

import excel "${path}\Raw Data\NIPA\detailnonres_inv2.xls", sheet("readme") cellrange(A15:P111) firstrow clear

drop if NAICSCodes == "--------"
rename BEACODE BEA
rename NAICSCodes NAICS
destring NAICS, replace

keep BEA NAICS INDUSTRYTITLE

gen ind = .
replace ind = 1 if BEA == "110C" | BEA == "113F"
replace ind = 2 if NAICS == "211" | NAICS == "212" | NAICS == "213"
replace ind = 3 if BEA == "2300" | BEA == "2200"
replace ind = 4 if BEA == "3210" | BEA == "3270" | BEA == "3310" | BEA == "3320" | BEA == "3330" | BEA == "3340" | BEA == "3350"| BEA == "336M" | BEA == "336O"| BEA == "3370" | BEA == "338A" | BEA == "311A" | BEA == "313T" | BEA == "315A"| BEA == "3220"| BEA == "3230" | BEA == "3240"| BEA == "3250"| BEA == "3260"
replace ind = 5 if NAICS == "42" | BEA == "44RT"
replace ind = 6 if BEA == "4810" | BEA == "4820" | BEA == "4830" | BEA == "4840" | BEA == "4850" | BEA == "4860" | BEA == "487S" | BEA == "4930"
replace ind = 7 if BEA == "5110" | BEA == "5120" | BEA == "5130" | BEA == "5140"
replace ind = 8 if BEA == "5210" | BEA == "5220" | BEA == "5230" | BEA == "5240" | BEA == "5250"
replace ind = 9 if BEA == "5310" | BEA == "5320" | BEA == "5411" | BEA == "5415" | BEA == "5412" | BEA == "5500" | BEA == "5610" | BEA == "5620"
replace ind = 10 if BEA == "6100"
replace ind = 11 if BEA == "7210" | BEA == "7220" | BEA == "711A" | BEA == "7130"
replace ind = 12 if BEA == "8100"
replace ind = 14 if BEA == "6210" | BEA == "622H" | BEA == "6230" | BEA == "6240"
 
save "${path}\Clean Data\NAICS-BEA.dta", replace

clear



local list 110C 113F 2110 2120 2130 2200 2300 3210 3270 3310 3320 3330 3340 3350 336M 336O 3370 338A 311A 313T 315A 3220 3230 3240 3250 3260 4200 44RT 4810 4820 4830 4840 4850 4860 487S 4930 5110 5120 5130 5140 5210 5220 5230 5240 5250 5310 5320 5411 5415 5412 5500 5610 5620 6100 6210 622H 6230 6240 711A 7130 7210 7220 8100

local List CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW CX CY CZ DA DB DC DD DE DF DG DH DI DJ DK DL DM DN DO DP DQ

foreach v in `list' {
import excel "${path}\Raw Data\NIPA\detailnonres_inv2.xls", sheet("`v'") cellrange(A7:DQ106) firstrow clear
drop C-CB
gen AvgResp`v' = 0
local i = 1

foreach k in `List' {
	
gen total = `k' if B == "TOTAL EQUIPMENT"
gen respon = `k' if A == "ET30" | A == "ET40" | A == "EI22" |	A == "EO22" | A == "EO40" | A == "EI60" | A == "EI30" | A == "EP12" | A == "ET50" | A == "ET11" | A == "ET12" 
egen responsive = sum(respon) 
egen avgresponse = max(responsive/total)

replace AvgResp`v' = avgresponse if _n == `i'
local i = `i'+1
drop total respon responsive avgresponse
	
}
egen Avg = mean(AvgResp`v') if AvgResp`v' != 0
sum Avg
matrix Avg`v' = r(mean)

}

use "${path}\Clean Data\NAICS-BEA.dta", clear

gen Avg = 0

local list 110C 113F 2110 2120 2130 2200 2300 3210 3270 3310 3320 3330 3340 3350 336M 336O 3370 338A 311A 313T 315A 3220 3230 3240 3250 3260 4200 44RT 4810 4820 4830 4840 4850 4860 487S 4930 5110 5120 5130 5140 5210 5220 5230 5240 5250 5310 5320 5411 5415 5412 5500 5610 5620 6100 6210 622H 6230 6240 711A 7130 7210 7220 8100

foreach v in `list' {
	
svmat Avg`v', name(A1)
egen A2 = min(A1)
replace Avg = A2 if BEA == "`v'"
drop A1 A2

}

drop if Avg == 0
collapse Avg, by(ind)
sort Avg ind

gen Top = 0
replace Top = 1 if Avg > .26

save "${path}\Clean Data\Responsive Industries.dta", replace


